﻿using Inaction;
using Inaction.Core;
using Inaction.Criteria;
using Inaction.Resource.Properties;
using Inaction.Rule;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;

namespace Inaction.Mapping
{
    [Serializable]
    public class SqlStatement
    {
        #region 变更，属性
        public string SqlText
        {
            get
            {
                return selectSql
                + (string.IsNullOrEmpty(insertText) ? string.Empty : " " + insertText)
                + (string.IsNullOrEmpty(updateText) ? string.Empty : " " + updateText)
                + (string.IsNullOrEmpty(deleteText) ? string.Empty : " " + deleteText)
                + (string.IsNullOrEmpty(whereText) ? string.Empty : " " + whereText);
            }
        }
        string selectSql = string.Empty;

        public string SelectSql
        {
            get { return selectSql; }
            internal set { selectSql = value; }
        }
        string insertText = string.Empty;

        public string InsertText
        {
            get { return insertText; }
            internal set { insertText = value; }
        }
        string updateText = string.Empty;

        public string UpdateText
        {
            get { return updateText; }
            internal set { updateText = value; }
        }
        string deleteText = string.Empty;

        public string DeleteText
        {
            get { return deleteText; }
            internal set { deleteText = value; }
        }
        string whereText = string.Empty;
        public string WhereText
        {
            get { return whereText; }
            internal set { whereText = value; }
        }

        SqlStatementType statementType = SqlStatementType.Select;

        public SqlStatementType StatementType
        {
            get { return statementType; }
            internal set { statementType = value; }
        }

        List<ParaValue> paraValues = new List<ParaValue>();
        public List<ParaValue> ParaValues
        {
            get { return paraValues; }
            set { paraValues = value; }
        }

        private Int32 recordCount = 0;
        internal Int32 RecordCount
        {
            get { return recordCount; }
            set { recordCount = value; }
        }

        public string Name { get; set; }

        static Dictionary<Type, SqlStatement> SelecteSqlCache = new Dictionary<Type, SqlStatement>();

        #endregion

        #region 构造
        public SqlStatement()
        { }

        private SqlStatement(Type type)
        {
            var classInfo = ClassInfo.LoadInfo(type);
            if (!string.IsNullOrEmpty(classInfo.SelectSql))
            {
                this.selectSql = classInfo.SelectSql;
            }
            else
            {
                string sql = "Select " + string.Join(",", classInfo.MappedProperties.Values.Select(p => p.Column.FormatName));
                if (!string.IsNullOrEmpty(classInfo.SelectView))
                {
                    sql += " From " + classInfo.SelectView + " ";
                }
                else
                {
                    sql += " From " + classInfo.Table.FormatName + " ";
                }
                this.selectSql = sql;
            }

            AddDefaultCriteriaValue(classInfo);

            if (classInfo.DeletedProperty != null
                && classInfo.DeletedProperty.Column != null
                && !paraValues.Any(p => p.Name == classInfo.DeletedProperty.Column.Name))
            {
                whereText = "Where ";
                if (whereText != "Where ")
                {
                    whereText += " And ";
                }
                whereText += classInfo.DeletedProperty.Column.NotEqualsClause;
                ParaValues.Add(new ParaValue { Name = classInfo.DeletedProperty.Column.Name, Value = true });
            }
        }
        #endregion

        #region 生成ExecuteScalarSql语句
        internal static SqlStatement CreateExecuteScalarSql(Type type, string sqlClause, IProperty property, string whereStr, List<ParaValue> paras)
        {
            var sql = new SqlStatement();
            var classInfo = ClassInfo.LoadInfo(type);
            sql.SelectSql = "Select " + string.Format(sqlClause, property.Column.FormatName) + " From " + classInfo.FormatTableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql.WhereText = "Where " + whereStr;
                sql.ParaValues = paras;
            }
            sql.AddDefaultCriteriaValue(classInfo);
            return sql;
        }
        internal static SqlStatement CreateExecuteScalarSql(Type type, string sqlClause, string whereStr, List<ParaValue> paras)
        {
            var sql = new SqlStatement();
            var classInfo = ClassInfo.LoadInfo(type);
            sql.SelectSql = "Select " + sqlClause + " From " + classInfo.FormatTableName;
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql.WhereText = "Where " + whereStr;
                sql.ParaValues = paras;
            }
            sql.AddDefaultCriteriaValue(classInfo);
            return sql;
        }

        #endregion

        #region 生成Selecte语句
        internal static string GetSelectSql<T>() where T : Model<T>, new()
        {
            var classInfo = ClassInfo.LoadInfo(typeof(T));
            var sql = string.Empty;
            if (!string.IsNullOrEmpty(classInfo.SelectSql))
            {
                sql = classInfo.SelectSql;
            }
            else
            {
                sql = "Select " + string.Join(",", classInfo.MappedProperties.Values.Select(p => p.Column.FormatName));
                sql += " From " + classInfo.Table.FormatName + " ";
            }

            return sql;
        }

        public static SqlStatement CreateSelecte(Type type, string whereStr, List<ParaValue> paras)
        {
            var sql = new SqlStatement(type);
            if (!string.IsNullOrEmpty(whereStr))
            {
                if (string.IsNullOrEmpty(sql.whereText))
                {
                    sql.WhereText = "Where ";
                }
                else
                {
                    sql.WhereText += " And ";
                }
                sql.whereText += whereStr;
            }
            if (paras != null)
            {
                sql.ParaValues.AddRange(paras);
            }
            return sql;
        }

        #region GetSelectStatementWithPara

        public static SqlStatement GetSelectStatement(Type type, CriteriaBase criteria = null)
        {
            SqlStatement sql = new SqlStatement(type);
            sql.AddWhereCriteria(criteria);
            return sql;
        }
        public static SqlStatement GetSelectStatementWithColumnPara(Type type, CriteriaBase criteria, params ParaValue[] paras)
        {
            var statement = GetSelectStatement(type, criteria);
            if (paras != null && paras.Count() != 0)
            {
                statement.whereText = " Where ";
                var classInfo = ClassInfo.LoadInfo(type);
                foreach (var para in paras)
                {
                    if (!classInfo.ColumnProperties.ContainsKey(para.Name))
                    {
                        throw new Exception(string.Format(Resources.ColumnNotFindInTable, classInfo.TableName, para.Name));
                    }
                    var property = classInfo.ColumnProperties[para.Name];
                    var count = statement.paraValues.Count(p => p.Name == para.Name);
                    statement.whereText += property.Column.FormatName + " = " + DbProvider.Instance.ParaFix + para.Name + (count == 0 ? "" : count.ToString());
                    statement.ParaValues.Add(para);
                }
            }
            return statement;
        }

        public static SqlStatement GetSelectStatementWithPropertyPara(Type type, CriteriaBase criteria, params ParaValue[] paras)
        {
            var statement = GetSelectStatement(type, criteria);
            if (paras != null && paras.Count() != 0)
            {
                statement.whereText = " Where ";
                var classInfo = ClassInfo.LoadInfo(type);
                foreach (var para in paras)
                {
                    if (!classInfo.NameProperties.ContainsKey(para.Name))
                    {
                        throw new Exception(string.Format(Resources.PropertyNotRegistered, para.Name));
                    }
                    var property = classInfo.NameProperties[para.Name];
                    if (property.Column == null)
                    {
                        throw new Exception(string.Format(Resources.NoFieldMappingWithProperty, para.Name));
                    }
                    var count = statement.paraValues.Count(p => p.Name == para.Name);
                    statement.whereText += property.Column.FormatName + " = " + DbProvider.Instance.ParaFix + para.Name + (count == 0 ? "" : count.ToString());
                    statement.ParaValues.Add(para);
                }
            }
            return statement;
        }
        #endregion
        #endregion

        #region 生成分页Select语句
        public static SqlStatement GetSelectStatement(Type type, int pageSize, int pageIndex, CriteriaBase criteria = null)
        {
            SqlStatement sql = new SqlStatement(type);
            sql.AddWhereCriteria(criteria);
            var classInfo = ClassInfo.LoadInfo(type);
            if (classInfo.Properties.Count == 0) return null;

            string columns = string.Empty;
            columns = string.Join(",", classInfo.MappedProperties.Values.Where(p => p.Mapping).Select(p => p.Column.FormatName));
            if (classInfo.DeletedProperty != null
                && classInfo.DeletedProperty.Column != null
                && !sql.ParaValues.Any(p => p.Name == Inaction.DbProvider.Instance.ParaFix + PropertyKeyWord.Deleted))
            {
                if (!string.IsNullOrEmpty(sql.whereText))
                {
                    sql.whereText += " And ";
                }
                sql.whereText += sql.whereText + classInfo.DeletedProperty.Column.NotEqualsClause;
                sql.ParaValues.Add(new ParaValue { Name = classInfo.DeletedProperty.Column.ParameterName, Value = true });
            }
            var pagedSelecte = Inaction.DbProvider.Instance.GetPageSql(type, pageSize, pageIndex);
            sql.SelectSql = string.Format(pagedSelecte, columns, string.IsNullOrEmpty(sql.whereText) ? string.Empty : " Where " + sql.whereText);
            sql.whereText = string.Empty;
            return sql;
        }
        #endregion

        #region 生成唯一索引的检查
        public static List<SqlStatement> GetUniqueStatement<T>(T t) where T : ModelBase
        {
            var classInfo = ClassInfo.LoadInfo(typeof(T));
            if (classInfo.Properties.Count == 0) return null;
            if (classInfo.Table.UniqueKeys.Count == 0) return null;

            List<SqlStatement> sqls = new List<SqlStatement>();
            foreach (var uk in classInfo.Table.UniqueKeys)
            {
                var properties = from item in classInfo.MappedProperties.Values
                                 from item1 in uk.Columns
                                 where item.Mapping
                                 && item.Column.Table.Name.Equals(item1.Table.Name)
                                 && item.Column.Name.Equals(item1.Name)
                                 select item;

                if (properties.Count() == 0) continue;
                SqlStatement sql = new SqlStatement();

                sql.SelectSql = "Select Count(*) From " + classInfo.Table.FormatName;
                sql.WhereText = "Where " + classInfo.PrimaryKey.Column.FormatName + " <> " + Inaction.DbProvider.Instance.ParaFix + classInfo.PrimaryKey.Column.Name;
                sql.ParaValues.Add(new ParaValue
                {
                    Name = classInfo.PrimaryKey.Column.Name,
                    Value = t.PrimaryKey
                });

                foreach (var p in properties)
                {
                    var val = p.GetFieldValue(t);
                    if (val == null || val.Equals(string.Empty))
                    {
                        sql.WhereText += " And " + p.Column.FormatName + " IS Null";
                    }
                    else
                    {
                        sql.WhereText += " And " + p.Column.EqualsClause;
                        sql.ParaValues.Add(new ParaValue { Name = p.Column.ParameterName, Value = val });
                    }
                }
                sql.Name = uk.Name;
                sqls.Add(sql);
            }
            return sqls;
        }
        public static List<SqlStatement> GetForeignKeyChildrenStatement<T>(T t) where T : ModelBase
        {
            var classInfo = ClassInfo.LoadInfo(typeof(T));
            if (classInfo.Properties.Count == 0) return null;
            if (classInfo.Table.UniqueKeys.Count == 0) return null;

            List<SqlStatement> sqls = new List<SqlStatement>();
            var whereValue = t.PrimaryKey;
            foreach (var fkc in classInfo.Table.ForeignKeyChildren)
            {
                SqlStatement sql = new SqlStatement();
                sql.SelectSql = "Select Count(*) From " + fkc.FormatName;
                var fk = fkc.ForeignKeys.FirstOrDefault(p => p.ReferenceTable.Name.Equals(classInfo.Table.Name));
                sql.WhereText = "Where " + fk.Column.EqualsClause;
                sql.ParaValues.Add(new ParaValue
                {
                    Name = fk.Column.ParameterName,
                    Value = whereValue
                });
                sql.Name = fkc.DisplayName;
                sqls.Add(sql);
            }
            return sqls;
        }
        #endregion

        #region 方法
        protected void AddDefaultCriteriaValue(ClassInfo classInfo)
        {
            var cv = classInfo.DefaultCriteriaValues;
            if (cv != null)
            {
                if (string.IsNullOrEmpty(whereText))
                    whereText = "Where ";
                foreach (var item in cv)
                {
                    whereText += item.Key.FormatName + Rule.EnumDescription.GetDescription(CriteriaOperate.In);
                    List<string> paraNames = new List<string>();
                    foreach (var val in item.Value)
                    {
                        var paraName = Inaction.DbProvider.Instance.ParaFix + paraValues.Count;
                        var paraValue = new ParaValue
                        {
                            Name = paraName,
                            Value = val
                        };
                        paraValues.Add(paraValue);
                        paraNames.Add(paraName);
                    }
                    whereText += "(" + string.Join(",", paraNames) + ")";
                }
            }
        }
        private void AddWhereCriteria(CriteriaBase criteria)
        {
            if (criteria == null) return;
            this.recordCount = criteria.RecordCount;
            var criteriaFields = criteria.LoadCriteriaFields();
            string whereText = string.Empty;
            foreach (var item in criteriaFields)
            {
                var field = item.Key;
                var criteriaField = item.Value;
                var value = field.GetValue(criteria);
                if (value == null || (value is string) && value.ToString().Length == 0) continue;
                if (whereText.Length != 0)
                {
                    whereText += " " + criteriaField.Logic + " ";
                }

                if (value is CriteriaBase)
                {
                    AddWhereCriteria(value as CriteriaBase);
                }

                if (criteriaField.Operate == CriteriaOperate.IsNull)
                {
                    whereText += criteriaField.FormatColumnName;
                    if ((bool)value)
                    {
                        whereText += " Is Null ";
                    }
                    else whereText += " Is Not Null ";
                }
                else if (criteriaField.Operate == CriteriaOperate.In)
                {
                    var fieldStr = "(" + criteriaField.FormatColumnName + " Is Not Null And " + criteriaField.FormatColumnName + " In ({0}) )";
                    var valStr = string.Empty;
                    if (value is Array)
                    {
                        var array = (Array)value;
                        for (int i = 0; i < array.Length; i++)
                        {
                            var pname = Inaction.DbProvider.Instance.ParaFix + criteriaField.ColumnName + i;
                            paraValues.Add(
                                new ParaValue
                                {
                                    Name = pname,
                                    Value = array.GetValue(i)
                                });
                            if (valStr != string.Empty)
                            {
                                valStr += " , ";
                            }
                            valStr += pname;
                        }
                    }
                    else
                    {
                        var pname = Inaction.DbProvider.Instance.ParaFix + criteriaField.ColumnName + '0';
                        paraValues.Add(
                            new ParaValue
                            {
                                Name = pname,
                                Value = value
                            });
                        valStr += pname;
                    }
                    if (!string.IsNullOrEmpty(valStr))
                    {
                        whereText += string.Format(fieldStr, valStr);
                    }
                }
                else
                {
                    whereText += "(" + criteriaField.FormatColumnName + " Is Not Null And " + criteriaField.FormatColumnName;
                    string oprateStr = EnumDescription.GetDescription(criteriaField.Operate);
                    whereText += oprateStr + Inaction.DbProvider.Instance.ParaFix + field.Name + ")";
                    paraValues.Add(new ParaValue
                    {
                        Name = field.Name,
                        Value = field.GetValue(criteria)
                    });
                }
            }
            if (!string.IsNullOrEmpty(whereText))
            {
                if (string.IsNullOrEmpty(this.whereText))
                {
                    this.whereText += " Where ";
                }
                else
                {
                    this.whereText += " And ";
                }
                this.whereText += whereText;
            }
        }
        public void AddWhereClause(string whereClause, List<ParaValue> paras)
        {
            if (!string.IsNullOrEmpty(whereClause))
            {
                if (string.IsNullOrEmpty(whereText)) whereText += "Where ";
                whereText += whereClause;
            }
            if (paras != null)
            {
                ParaValues.AddRange(paras);
            }
        }
        public void AddWhereClause(params KeyValuePair<IProperty, object>[] properties)
        {
            foreach (var item in properties)
            {
                var property = item.Key;
                var value = item.Value;
                if (value == null) continue;
                if (value is string)
                {
                    if (string.IsNullOrEmpty(value.ToString())) continue;
                }
                var count = ParaValues.Count(p => p.Name == property.Name);
                var paraName = property.Name + (count == 0 ? "" : count.ToString());
                if (!string.IsNullOrEmpty(whereText))
                {
                    whereText += " And ";
                }
                else
                {
                    whereText = " Where ";
                }
                whereText += property.Column.FormatName + "=" + property.Column.ParameterName + (count == 0 ? "" : count.ToString());
                ParaValues.Add(new ParaValue { Name = paraName, Value = value });
            }
        }
        public new string ToString()
        {
            string str =
                (selectSql == string.Empty ? "" : selectSql + "\r\n")
                + (insertText == string.Empty ? "" : insertText + "\r\n")
                + (updateText == string.Empty ? "" : updateText + "\r\n")
                + (deleteText == string.Empty ? "" : deleteText + "\r\n")
                + (whereText == string.Empty ? "" : whereText + "\r\n");
            string paras = string.Empty;
            if (this.paraValues.Count != 0)
            {
                paras = "Parameters:";
                foreach (var para in this.ParaValues)
                {
                    paras += " ";
                    paras += para.Name + ":" + para.Value.ToString();
                }
            }
            return str + paras;
        }
        #endregion
    }
    [Serializable]
    public class ParaValue
    {
        string name = string.Empty;
        public string Name
        {
            get { return name; }
            set
            {
                name = value;
                if (!name.StartsWith(DbProvider.Instance.ParaFix))
                    name = DbProvider.Instance.ParaFix + name;
            }
        }

        object val = null;
        public object Value
        {
            get { return val; }
            set
            {
                val = Util.TypeConvert.ConvertToDbValue(value);
            }
        }

        public override string ToString()
        {
            return Name + ":" + Value.ToString();
        }
    }
}
